import pymysql
import tushare as ts
import datetime
from apscheduler.schedulers.blocking import BlockingScheduler



def update_daily_hist(table_name,code):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()

    now_date = datetime.datetime.now().strftime('%Y-%m-%d')
    daily_hist = ts.get_k_data(code,start=now_date,end=now_date)

    if daily_hist != None:
        data = daily_hist.iloc[0]
        date = data['date']
        open = data['open']
        close = data['close']
        high = data['high']
        low = data['low']
        volume = data['volume']
        code = data['code']
        insert_data = (date,open,close,high,low,volume,code)
        update_daily_sql = "INSERT INTO {0} (DATE,OPEN,CLOSE,HIGH,LOW,VOLUME,CODE) VALUES {1}".format(table_name, insert_data)
        cursor.execute(update_daily_sql)
        db.commit()
    else:
        pass
    db.close()

def get_stock_code(table_name):
    db = pymysql.connect('localhost', 'root', 'minicooper', 'stock_info', charset='utf8')
    cursor = db.cursor()
    query_sql_sql = "SELECT CODE FROM {0}".format(table_name)
    cursor.execute(query_sql_sql)
    result = cursor.fetchall()
    db.close()
    return result


def daily_run():
    for code_tuple in get_stock_code(stock_basics):
        code = code_tuple[0]
        update_daily_hist(stoch_hist, code)


if __name__ == '__main__':
    schduler = BlockingScheduler()
    schduler.add_job(daily_run,'cron',day_of_week='mon-fri',hour='15',minute='1')
    try:
        schduler.start()
    except (KeyboardInterrupt,SystemExit):
        schduler.shutdown()
